﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Data;
using System.Data.Common;
using System.Text.RegularExpressions;
using Common;
using Business;
using WF_Business;

namespace SbBusiness.Wsbs
{
    /// <summary>
    /// 消息
    /// </summary>
    public class Messagebox
    {
        /// <summary>
        /// 添加消息
        /// </summary>
        /// <param name="strMessage">消息内容</param>
        /// <param name="strPhoneNo">手机号码</param>
        /// <param name="strUserName">用户名称</param>
        /// <param name="strUserID">用户ID</param>
        /// <returns></returns>
        public int InsertMeaasge(string strMessage, string strPhoneNo, string strUserName,string strUserID)
        {
            strUserName = strUserName.ToUpperInvariant();
            string strSql = string.Format(@"Insert into xt_messagebox(MESSAGETEXT,PHONENO,USERNAME,USERID) 
                values('{0}','{1}','{2}','{3}')", strMessage, strPhoneNo, strUserName, strUserID);

            return SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 修改消息
        /// </summary>
        /// <param name="strMessage">消息内容</param>
        /// <param name="strPhoneNo">手机号码</param>
        /// <param name="strUserName">用户名称</param>
        /// <param name="strUserID">用户ID</param>
        /// <param name="strMsgID">消息ID</param>
        /// <returns></returns>
        /// <!--add by zhongjian 20091125-->
        public int UpdateMeaasge(string strMessage, string strPhoneNo, string strUserName, string strUserID,string strMsgID)
        {
            strUserName = strUserName.ToUpperInvariant();
            string strSql = string.Format(@"update xt_messagebox set MESSAGETEXT='{0}',PHONENO='{1}',USERNAME='{2}',USERID='{3}',REALSENDTIME=sysdate
                            where messageid='{4}'", strMessage, strPhoneNo, strUserName, strUserID, strMsgID);

            return SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 判断消息是否存在,并返回消息ID
        /// </summary>
        /// <param name="strStepNo">消息步骤ID</param>
        /// <param name="strUserID">用户ID</param>
        /// <returns>为空者不存在</returns>
        public string GetMessageID(string strStepNo, string strUserID)
        {
            string strSql = string.Format(@"select a.messageid from xt_messagebox a,sys_user b where a.userid=b.userid
                            and b.stepno='{0}' and b.userid='{1}'", strStepNo, strUserID);

            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 获取消息
        /// </summary>
        /// <param name="strUserName">UserName</param>
        /// <param name="strUserID">用户ID</param>
        /// <returns></returns>
        public DataTable GetMessage(string strUserName, string strUserID)
        {
            string strSql = string.Format(@"select * from xt_messagebox where USERNAME='{0}' 
                and USERID='{1}' order by PLANSENDTIME asc", strUserName, strUserID);

            DataTable dtTemp;
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 获取消息个数
        /// </summary>
        /// <param name="strUserId">UserId</param>
        /// <returns></returns>
        public string GetMessageCount(string strUserId)
        {
            string strSql = string.Format(@"select count(*) from xt_messagebox where 
                USERNAME=(select username from sys_user where userid='{0}') and USERID='{0}' and READTYPE='0'", 
                strUserId);
            strSql = string.Format(@"select count(*) from xt_messagebox where USERID='{0}' and READTYPE='0'", strUserId);
            
            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 根据获取消息.
        /// </summary>
        /// <param name="strUserName">UserName</param>
        /// <param name="strUserID">用户ID</param>
        /// <param name="strFromPlan">起始发送时间.</param>
        /// <param name="strToPlan">结束发送时间.</param>
        /// <param name="strReadType">读取状态</param>
        /// <returns></returns>
        public DataTable GetMessageInfo(string strUserName, string strFromPlan, string strToPlan, string strUserID,string strReadType)
        {
            string strSql = string.Format(@"select rownum,messageid,messagetext,phoneno,READTYPE,
                to_char(plansendtime,'yyyy-mm-dd') plansendtimetext,username from xt_messagebox a 
                where USERID='{0}'", strUserID);
            if (!string.IsNullOrEmpty(strFromPlan))
            {
                strSql += string.Format(" and PLANSENDTIME >= to_date('{0}','yyyy-mm-dd')", strFromPlan);
            }
            if (!string.IsNullOrEmpty(strToPlan))
            {
                strSql += string.Format(" and PLANSENDTIME <= to_date('{0}','yyyy-mm-dd')", strToPlan);
            }
            if (!string.IsNullOrEmpty(strReadType))
            {
                strSql += string.Format(" and READTYPE ='{0}'", strReadType);
            }
            strSql += "order by PLANSENDTIME desc";

            DataTable dtTemp;
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 根据消息id获取消息值
        /// </summary>
        /// <param name="strMsgId"></param>
        /// <returns></returns>
        public DataTable GetMessageBoxById(string strMsgId)
        {
            string strSql = string.Format(@"select messageid,messagetext,phoneno,issended,READTYPE,
                plansendtime,realsendtime,username from xt_messagebox where messageid='{0}'", strMsgId);
            DataTable dtTemp;
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 删除消息
        /// </summary>
        /// <param name="strMsgId">消息ID</param>
        /// <!--addby zhongjian 20100129-->
        public void DeleteMessage(string strMsgId)
        {
            string strSql = string.Format(@"delete xt_messagebox where messageid='{0}'", strMsgId);
            SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="strMsgId">消息ID</param>
        /// <param name="strReadType">阅读类型（0：未读；1：已读）</param>
        /// <!--addby zhongjian 20100129-->
        public void UpdateMessageType(string strMsgId,string strReadType)
        {
            string strSql = string.Format(@"update xt_messagebox set READTYPE='{1}' where messageid='{0}'", strMsgId, strReadType);
            SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 获取消息设置信息
        /// </summary>
        /// <param name="iStepNo">消息步骤</param>
        /// <param name="strStepName">消息名称</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20091120-->
        public DataTable GetRequestSet(int iStepNo, string strStepName)
        {
            string strSql = string.Format(@"select step_no,step_msg from xt_request_step where 1=1 ");
            if (iStepNo>0)
                strSql += string.Format(" and  step_no='{0}'", iStepNo);
            if (!string.IsNullOrEmpty(strStepName))
                strSql += string.Format(" and  step_name='{0}'", strStepName);
            DataTable dtTemp;
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 修改用户消息流水号
        /// </summary>
        /// <param name="strUserID">用户ID</param>
        /// <param name="StepNo">消息流水号</param>
        public int UpdateUserStepNO(string strUserID,string StepNo)
        {
            string strSql = string.Format(@"update sys_user set stepno='{1}' where userid='{0}'", strUserID, StepNo);
            return SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 获取最新的消息信息内容
        /// </summary>
        /// <param name="strUserID">用户ID</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20091202-->
        public string GetNewMessage(string strUserID)
        {
            string strSql = string.Format(@"select messagetext from xt_messagebox a 
                where USERID='{0}' order by PLANSENDTIME desc", strUserID);

            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 获取最新的消息的IID
        /// </summary>
        /// <param name="strUserID">用户ID</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20100202-->
        public string GetMessageIID(string strUserID)
        {
            string strSql = string.Format(@"select iid from xt_messagebox a 
                where USERID='{0}' order by PLANSENDTIME desc", strUserID);
            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 获取最新的消息的IID
        /// </summary>
        /// <param name="strUserID">用户ID</param>
        /// <param name="strIID">IID</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20100202-->
        public DataTable GetSubmitInfo(string strUserID, string strIID)
        {
            string strSql = string.Format(@"select *
                                              from xt_submit_info
                                             where requester = '{0}'
                                               and isdelete <> '1'
                                               and iid = '{1}'", strUserID, strIID);
            DataTable dtTemp;
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

    }
}
